
const db = require("../utils/db");
const util = require("../utils/util")

async function add_post(user_id, title, content, imgs, tags) { 
    var sql = "insert into post(user_id, title, content, read_num, imgs, tags, post_time) values(?,?,?,?,?,?,?)";
    var args = [user_id, title, content, 0, JSON.stringify(imgs), JSON.stringify(tags), util.datestring(new Date())];
    return (await db.query(sql, args)).insertId;
}

async function del_post(post_id) {
    var sql = `delete from post where post_id = ${post_id}`;
    await db.query(sql);
}

async function update_post(post_id, title, content, imgs, tags) {
    var sql = "update post set title=?,content=?,imgs=?,tags=? where post_id=?";
    var args = [title, content, JSON.stringify(imgs), JSON.stringify(tags), post_id];
    await db.query(sql, args);
}

async function add_read(post_id) {
    var sql = `update post set read_num=read_num+1 where post_id = ${post_id}`;
    await db.query(sql);
}

async function get_fullpost(user_id, post_id) {
    // 获取单个post的完整信息
    var sql = 
`
SELECT
    p.*,
    upvotes.upvote_num,
    upvotes.upvoted,
    stars.star_num,
    stars.starred,
    comments.comment_num
FROM
	(
		SELECT
			post.*,
			user.name AS user_name,
			user.avatar AS user_avatar
		FROM post, user
        WHERE post.user_id = user.user_id and
            post.post_id = ${post_id}
    ) p LEFT OUTER JOIN
    (
        SELECT
            post_id,
            count(*) AS upvote_num,
            if(sum(user_id = '${user_id}'), true, false) AS upvoted
        FROM upvote_post
        GROUP BY post_id
    ) upvotes
    ON p.post_id = upvotes.post_id
    LEFT OUTER JOIN
    (
        SELECT
            post_id,
            count(*) AS star_num,
            if(sum(user_id = '${user_id}'), true, false) AS starred
        FROM star
        GROUP BY post_id
    ) stars
    ON p.post_id = stars.post_id
    LEFT OUTER JOIN
    (
        SELECT
            post_id,
            count(*) AS comment_num
        FROM comment
        GROUP BY post_id
    ) comments
    ON p.post_id = comments.post_id
`;
    var res = (await db.query(sql))[0];
    res.imgs = JSON.parse(res.imgs);
    res.tags = JSON.parse(res.tags);
    return res;
}

async function get_user_id(post_id) {
    // 从post_id获取user_id，用以身份验证
    var sql = `SELECT user_id from post where post_id = ${post_id}`
    return (await db.query(sql))[0].user_id;
}

module.exports = {
    add_post: add_post,
    del_post: del_post,
    update_post: update_post,
    add_read: add_read,
    get_fullpost: get_fullpost,
    get_user_id: get_user_id,
}
